
package keter.framework.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * <p>
 * 完全独立的数据库直接操作工具
 * </p>
 * 
 * @author 顾力行 - gulx@neusoft.com
 * @version 1.0 Created on Apr 15, 2009 6:48:04 PM
 */
public class JdbcUtil{
	/**
	 * Logger for this class
	 */
	private static final Logger log = LoggerFactory.getLogger(JdbcUtil.class);
	private static Connection conn;
	private static Statement stmt;
	private static PreparedStatement pstmt;
	private static ResultSet rstr;
	private static String strConnectionURL = null;
	private static String username = null;
	private static String passwd = null;

	public static void init(String url,String username, String passwd){
		log.info(" init datasource context...");
		strConnectionURL = url;
		JdbcUtil.username = username;
		JdbcUtil.passwd = passwd;
	}

	/**
	 * <p>
	 * 执行查询
	 * </p>
	 * 
	 * @param strConnectionURL
	 * @param userNamePwd
	 * @param sql
	 * @param type
	 * @return
	 * @author: 顾力行 - gulx@neusoft.com
	 * @throws SQLException 
	 * @date: Created on Apr 27, 2009 10:44:07 AM
	 */
	public static ResultSet query(String sql) {
		prepareDBDriver();
		try {
			prepareQuery(sql);
		} catch (SQLException e) {
		    log.error("error:",e);
			throw new RuntimeException("bad sql:["+sql+"]");
		}
		return rstr;
	}
	
	public static ResultSet query(String sql,Connection conn) throws SQLException {
		stmt = conn.createStatement();
		rstr = stmt.executeQuery(sql);
		return rstr;
	}
	
	public static ResultSet query(String sql,String... params) {
	    prepareDBDriver();
	    try {
	        prepareQuery(sql,params);
	    } catch (SQLException e) {
	        log.error("error:",e);
	        throw new RuntimeException("bad sql:["+sql+"]");
	        
	    }
	    return rstr;
	}

	public static void update(String sql) throws Exception {
		prepareDBDriver();
		prepareUpdate(sql);
	}
	
	public static void update(String sql,Connection conn) throws Exception {
		stmt = conn.createStatement();
		stmt.executeUpdate(sql);
	}
	
	
	public static enum DBType{
		MYSQL,SQLITE,OTHER
	}
	
	public static  DBType getDBType(String dbUrl) {
	    if(dbUrl.contains("mysql")){
	        return DBType.MYSQL;
	    }
	    else if(dbUrl.contains("sqlite")){
	    	 return DBType.SQLITE;
	    }
	    return DBType.OTHER;
} 

	/**
	 * <p>
	 * Method ：prepare
	 * <p>
	 * Description : 构建数据库驱动
	 * 
	 * @author 顾力行-gulx@neusoft.com
	 *         <p>
	 *         --------------------------------------------------------------<br>
	 *         修改履历：<br>
	 *         <li>2010-8-30，gulx@neusoft.com，创建方法；<br>
	 *         --------------------------------------------------------------
	 *         <br>
	 *         </p>
	 */
	private static void prepareDBDriver() {
		try {
		    if(strConnectionURL.contains("oracle")){
		        Class.forName("oracle.jdbc.driver.OracleDriver");
		    }
		    else if(strConnectionURL.contains("hsqldb")){
		        Class.forName("org.hsqldb.jdbcDriver");
		    }
		    else if(strConnectionURL.contains("mysql")){
		        Class.forName("com.mysql.jdbc.Driver");
		    }
		} catch (ClassNotFoundException e) {
	        log.error("error:",e);
		}
	}

	/**
	 * <p>
	 * 结果集准备
	 * </p>
	 * 
	 * @param strConnectionURL
	 * @param userNamePwd
	 * @param sql
	 * @throws SQLException
	 * @author: 顾力行 - gulx@neusoft.com
	 * @date: Created on Apr 20, 2009 4:55:34 PM
	 */
	private static void prepareQuery(String sql) throws SQLException {
			conn = DriverManager.getConnection(strConnectionURL, username, passwd);
			stmt = conn.createStatement();
			rstr = stmt.executeQuery(sql);
	}
	
	private static void prepareQuery(String sql,String... params) throws SQLException {
        conn = DriverManager.getConnection(strConnectionURL, username, passwd);
        pstmt = conn.prepareStatement(sql);
        int i=1;
        for (String param : params) {
            pstmt.setString(i, param);
            i++;
        }       
        rstr = pstmt.executeQuery();
    }

	/**
	 * <p>
	 * 结果集准备
	 * </p>
	 * 
	 * @param strConnectionURL
	 * @param userNamePwd
	 * @param sql
	 * @throws SQLException
	 * @author: 顾力行 - gulx@neusoft.com
	 * @throws Exception 
	 * @date: Created on Apr 20, 2009 4:55:34 PM
	 */
	private static void prepareUpdate(String sql) throws Exception {
		try {
			conn = DriverManager.getConnection(strConnectionURL, username, passwd);
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			throw new Exception(e);
		}
	}

	/**
	 * <p>
	 * 关闭连接 需要在结果集使用完成之后操作
	 * </p>
	 * 
	 * @author: 顾力行 - gulx@neusoft.com
	 * @date: Created on Apr 20, 2009 4:57:13 PM
	 */
	public static void close() {
		if (conn != null && stmt != null) {
			try {
				conn.close();
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void main(String args[]) throws SQLException {
//		Properties p = new PropertiesLoader("classpath:/database.properties").getProperties();  
//		System.out.println(p.getProperty("jdbc.url"));
		String sql = "";
//		/* mysql test*/
//		  JdbcUtil.init("jdbc:mysql://127.0.0.1:3306/app-test?useUnicode=true&characterEncoding=utf-8","root","mysql");
//	      sql = "select count(1) from mysql.user;";
//	      ResultSet rstr = JdbcUtil.query(sql);
//	      while (rstr.next()) {
//	          String USR_HOST = rstr.getString(1);
//	          System.out.println("round1: " + USR_HOST);
//	      }
//	      
//	      sql = "select count(1) from mysql.user where user=?;";
//	      rstr = JdbcUtil.query(sql,"root");
//	      while (rstr.next()) {
//	          String USR_HOST = rstr.getString(1);
//	          System.out.println("round1: " + USR_HOST);
//	      }
//	      JdbcUtil.close();
		
		/* oracle test*/
		String url;
//		String url=
//		        "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)"
//		        + "(HOST=192.168.242.136)(PORT=1521)))"
//		        + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl_133)))";//集群正确但不推荐使用
//		url = "jdbc:oracle:thin:@192.168.180.55:1521:orcl11g";//error:集群的sid跟serviceName不一致
		url = "jdbc:oracle:thin:@//192.168.242.136:1521/orcl_133";//集群正确
//		url = "jdbc:oracle:thin:@192.168.242.133:1521:orcl11g2";//单机正确
		JdbcUtil.init(url,"sys as sysdba","Welcome1#");
		
//		url = "jdbc:oracle:thin:@//192.168.241.115:1521/enmo";//单机正确
//		url = "jdbc:oracle:thin:@192.168.241.115:1521:enmo";//单机正确
//		JdbcUtil.init(url,"zdb","oracle");
		
//		url = "jdbc:oracle:thin:@192.168.241.181:1521:swp";//单机正确
//		JdbcUtil.init(url,"swp","swp");
        
        sql = "select 1 from dual";
        rstr = JdbcUtil.query(sql);
        while (rstr.next()) {
            String USR_HOST = rstr.getString(1);
            System.out.println("round1: " + USR_HOST);
        }
        
//        sql = "select dname from dept where deptno=?";
//        rstr = JdbcUtil.query(sql,"10");
//        while (rstr.next()) {
//            String USR_HOST = rstr.getString(1);
//            System.out.println("round1: " + USR_HOST);
//        }
        JdbcUtil.close();
	}
}